package com.hellogood.yaohuo.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.ConnectionPool;
import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.shared.vo.BasicVo;

public class CommonDAO {

	Logger log = Logger.getLogger(this.getClass());

	public BasicVo getBasicVo(String code, String orgCode) {
		BasicVo vo = null;
		String hxDbName = ConnectionPool.hxDbName;
		String sql = "if  exists(select a.plucode from " + hxDbName + "..goods a where (a.plucode='1100001 ' or a.barcode='1100001 '  ))"
				+ " begin select a.plucode,a.barcode,a.pluname,a.depcode,(SELECT depName FROM " + hxDbName
				+ "..dept WHERE DepCode = a.depcode) AS depName," + " a.clscode,(SELECT clsName FROM " + hxDbName
				+ "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,a.psPrice," + " (select g.ojprice from " + hxDbName
				+ "..goodsorg g where g.orgcode='" + orgCode + "'" + " and g.plucode=(select a.plucode from " + hxDbName
				+ "..goods a where  a.barcode='" + code + "' or a.plucode='" + code + "')) as jprice ," + " (select g.price from " + hxDbName
				+ "..goodsorg g where g.orgcode='" + orgCode + "' and g.plucode=(select a.plucode from " + hxDbName + "..goods a"
				+ " where  a.barcode='" + code + " ' or a.plucode='" + code + " ')) as price from " + hxDbName + "..goods a" + " where  a.plucode='"
				+ code + " '" + " end else  begin select a.plucode,(select b.barcode from " + hxDbName + "..barcoderef b where b.barcode='" + code
				+ " ') as barcode ,a.pluname,a.depcode, (SELECT depName FROM " + hxDbName + "..dept WHERE DepCode = a.depcode) AS depName,a.clscode,"
				+ "(SELECT clsName FROM " + hxDbName + "..GClass WHERE ClsCode = a.clscode) AS clsName,a.unit,a.psPrice, "
				+ " (select g.ojprice from " + hxDbName + "..goodsorg g where g.orgcode='" + orgCode + "' and" + " g.plucode=(select b.plucode from "
				+ hxDbName + "..barcoderef b where b.barcode='" + code + "')) as jprice ," + " (select g.price from " + hxDbName
				+ "..goodsorg g where g.orgcode='" + orgCode + "' and  g.plucode=(select b.plucode from " + hxDbName + "..barcoderef b"
				+ " where b.barcode='" + code + "')) as price" + "  from " + hxDbName + "..goods a  where a.plucode=(select b.plucode from "
				+ hxDbName + "..barcoderef b where b.barcode='" + code + " ') end";

		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				vo = new BasicVo();
				vo.setBarCode(rs.getString("barCode"));
				vo.setDepCode(rs.getString("DepCode"));
				vo.setDepName(rs.getString("depName") == null ? "" : rs.getString("depName").trim());
				vo.setPluCode(rs.getString("pluCode"));
				vo.setPluName(rs.getString("pluName") == null ? "" : rs.getString("pluName").trim());
				vo.setUnit(rs.getString("Unit"));
				vo.setjPrice(rs.getBigDecimal("jPrice"));
				vo.setPsPrice(rs.getBigDecimal("psPrice"));
				vo.setPrice(rs.getBigDecimal("price"));
				vo.setClsCode(rs.getString("clsCode"));
				vo.setClsName(rs.getString("clsName") == null ? "" : rs.getString("clsName").trim());
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return vo;
	}

	// 检查版本号
	public boolean checkVersion() {
		String sql = "select * from VERSION where version = '1.0603'";
		boolean yes = false;
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				yes = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return yes;
	}
}
